package com.midi.wholesale.main;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.midi.wholesale.main.*;
import com.mysql.jdbc.Statement;

public class addElements {

	private static Statement st = null;
	private static Statement stOne = null;
	private static Statement stTwo = null;
	private static Statement stFour = null;
	private static Statement stFive = null;

	private static PreparedStatement stSix = null;
	private static PreparedStatement stSeven = null;
	private static PreparedStatement stEight = null;
	private static PreparedStatement stNine = null;
	private static PreparedStatement stTen = null;
	private static PreparedStatement stEleven = null;
	private static PreparedStatement stTwlve = null;
	private static PreparedStatement stThirteen = null;
	private static PreparedStatement stFourteen = null;
	private static PreparedStatement stFifteen = null;
	private static PreparedStatement stSixteen = null;
	private static PreparedStatement stSeventeen = null;

	private static int val;
	private static int regionid;

	// When initializing the Frame load data to list
	public static Object[] loadDataAddElementsFrame() {

		try {
			st = (Statement) Main.dbConnection.createStatement();
			stOne = (Statement) Main.dbConnection.createStatement();
			stTwo = (Statement) Main.dbConnection.createStatement();

		} catch (SQLException e) {
			System.out.println("connection error in addElement Class");
			e.printStackTrace();
		}

		// select data from agency, region, shop

		ResultSet agencyrs = null;
		ArrayList agencyList = new ArrayList();
		ResultSet regionrs = null;
		ArrayList regionList = new ArrayList();
		ResultSet shoprs = null;
		ArrayList shopList = new ArrayList();
		Object[] listObject = new Object[3];

		try {
			agencyrs = st.executeQuery("select name from agents");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			while (agencyrs.next()) {
				// agencyList=agencyList+agencyrs.next()+",";
				agencyList.add(agencyrs.getString("name"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			regionrs = stOne.executeQuery("select name from lorries");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			while (regionrs.next()) {
				regionList.add(regionrs.getString("name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			shoprs = stTwo.executeQuery("select name from routs");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			while (shoprs.next()) {
				shopList.add(shoprs.getString("name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		listObject[0] = agencyList;
		listObject[1] = regionList;
		listObject[2] = shopList;

		return listObject;
	}

	// Edit agency list with database data
	public static void editAgency(String agencyName, String actionPerformed) {

		if (actionPerformed == "add") {

			try {

				stSeven = (PreparedStatement) Main.dbConnection
						.prepareStatement("INSERT INTO agents (name) values (?)");
				stSeven.setString(1, agencyName);
				stSeven.executeUpdate();

			} catch (SQLException e) {
				System.out.println("error in add agency");
				e.printStackTrace();
			}

		}

		if (actionPerformed == "remove") {

			try {
				String sql = "delete from agents where name = ?";
				stNine = Main.dbConnection.prepareStatement(sql);
				stNine.setString(1, agencyName);
				int del = stNine.executeUpdate();

				// val =
				// st.executeUpdate("delete from agency where name='"+agencyName+"'");
			} catch (SQLException e) {
				System.out.println("error in add agency");
				e.printStackTrace();
			}

		}

	}

	// Edit region list with database data
	public static void editLorries(String regionName, String actionPerformed) {
		if (actionPerformed == "add") {

			try {
				stEight = (PreparedStatement) Main.dbConnection
						.prepareStatement("INSERT INTO lorries (name) values (?)");
				stEight.setString(1, regionName);
				stEight.executeUpdate();

			} catch (SQLException e) {
				System.out.println("error in add region");
				e.printStackTrace();
			}

		}

		if (actionPerformed == "remove") {

			try {
				String sql = "delete from lorries where name = ?";
				stTen = Main.dbConnection.prepareStatement(sql);
				stTen.setString(1, regionName);
				int del = stTen.executeUpdate();

			} catch (SQLException e) {
				System.out.println("error in remove regions");
				e.printStackTrace();
			}

		}

	}

	public static void editroutes(String regionName, String actionPerformed) {
		if (actionPerformed == "add") {

			try {
				stEight = (PreparedStatement) Main.dbConnection
						.prepareStatement("INSERT INTO routs (name) values (?)");
				stEight.setString(1, regionName);
				stEight.executeUpdate();

			} catch (SQLException e) {
				System.out.println("error in add region");
				e.printStackTrace();
			}

		}

		if (actionPerformed == "remove") {

			try {
				String sql = "delete from routs where name = ?";
				stTen = Main.dbConnection.prepareStatement(sql);
				stTen.setString(1, regionName);
				int del = stTen.executeUpdate();

			} catch (SQLException e) {
				System.out.println("error in remove regions");
				e.printStackTrace();
			}

		}

	}

	public static void edititems(String regionName, String actionPerformed) {
		if (actionPerformed == "add") {

			try {
				stEight = (PreparedStatement) Main.dbConnection
						.prepareStatement("INSERT INTO items (name) values (?)");
				stEight.setString(1, regionName);
				stEight.executeUpdate();

			} catch (SQLException e) {
				System.out.println("error in add region");
				e.printStackTrace();
			}

		}

		if (actionPerformed == "remove") {

			try {
				String sql = "delete from items where name = ?";
				stTen = Main.dbConnection.prepareStatement(sql);
				stTen.setString(1, regionName);
				int del = stTen.executeUpdate();

			} catch (SQLException e) {
				System.out.println("error in remove regions");
				e.printStackTrace();
			}

		}

	}

	public static String getPassword(int id) {
		ResultSet passwordResult;
		String PW = null;
		try {
			Statement newST = (Statement) Main.dbConnection.createStatement();
			passwordResult = newST
					.executeQuery("select value from config where id='" + id
							+ "'");
			passwordResult.next();
			PW = passwordResult.getString("value");

		} catch (SQLException e) {
			System.out.println("Error in password checker");
			e.printStackTrace();
		}
		return PW;
	}

}
